** TOTAL WORK IN PROGRESS **

This attempts to both outline the current status of the tidal culvert data and provide a task list for walking through the data collected to date and the data that are yet to be collected. Sort of a hybrid documentation of methods and to-do list.

library(knitr)
library(rmdformats)

## Global options
options(max.print="75")
opts_chunk$set(echo=FALSE,
                 cache=TRUE,
               prompt=FALSE,
               tidy=TRUE,
               comment=NA,
               message=FALSE,
               warning=FALSE)
opts_knit$set(width=75)

library(readr)
library(readxl)
library(tidyxl)
library(unpivotr)
library(tidyverse)
library(sf)
library(mapview)
library(rstudio)

tidalCulvert_datasheetsFolder <- "../../Box Sync/Tidal Assessments/Culvert Excel Sheets"
tidalCulvert_outputs <- "../../Box Sync/Tidal Assessments"
keysheet <- read_excel("../../Box Sync/Tidal Assessments/key.xlsx")
culvertPts_path <- "M:/Projects/LI/Culvert_Assessment/data/Tidal Crossings/TidalCrossings_20180712.shp" # Direct path to the shapefile used as base. Change as needed. # UPDATE:
LIculvert_GISpts <- st_read(culvertPts_path)
## Reading layer `TidalCrossings_20180712' from data source `M:\Projects\LI\Culvert_Assessment\data\Tidal Crossings\TidalCrossings_20180712.shp' using driver `ESRI Shapefile'
## Simple feature collection with 177 features and 15 fields
## geometry type:  POINT
## dimension:      XY
## bbox:           xmin: -1.797693e+308 ymin: -1.797693e+308 xmax: -8004521 ymax: 5034111
## epsg (SRID):    3857
## proj4string:    +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs
LIculvert_GISpts <- LIculvert_GISpts %>% mutate(crossingID = as.character(Tidal_ID))

cleanLIculvert_GISpts <- LIculvert_GISpts %>% filter(Latitude != 0)

source("functions/culvert_tidy.R")
source("functions/culvert_extract.R")    
LIculvertsAssessments <- culvert_tidy(tidalCulvert_datasheetsFolder) %>% 
  mutate(decoded = map(.x = tidycells, .f = ~decodeSheet(.x, keysheet))) # this is where the sausage is made. 


# unnest the tidyxl data to create a large tidy dataframe
LIculvertData <- LIculvertsAssessments %>% 
  select(filenames, decoded) %>% 
  unnest() %>% select(filenames, dataName, values) %>% 
  spread(key = dataName, value = values) %>% 
  select(filenames, crossingID, dateAssessed, observers, everything()) %>% # organize the order of the columns.
  mutate(dateAssessed = lubridate::parse_date_time(dateAssessed, orders = 'ymd'))



if(typeof(LIculvertData$crossingID) == typeof(LIculvert_GISpts$crossingID)){ # Are the two columns the same datatype?
  matchedLIculverts <- LIculvertData %>% filter(crossingID %in% LIculvert_GISpts$crossingID) %>% select(filenames, crossingID)
  missingLIculverts <- LIculvertData %>% filter(!crossingID %in% LIculvert_GISpts$crossingID) %>% select(filenames, crossingID)
}

Output

Here’s the output– I added an option to the table so that a local version can be saved- It’s a bit clumbsy here since it’s so wide (lots of variables) but you can scroll left/right to see all the columns- or click ‘Download’ to save a copy to open in Excel.

Missing Spatial Data by CrossingID

The following list contains filenames of datasheets that DO NOT have a corresponding location in shapefile (path: M:/Projects/LI/Culvert_Assessment/data/Tidal Crossings/TidalCrossings_20180712.shp).
Just a note that currently the only linkage between the field data sheets and the spatial data (the locations for culverts as derived from remotely sensed and groundtruthed data) are the CrossingID field. Care must be taken to ensure the names are entered correctly.

Mapped Culverts

Out of the pile of datasheets in the folder (../../Box Sync/Tidal Assessments/Culvert Excel Sheets) this map shows locations that have a corresponding datasheet. The map below are locations (though some inaccuracies are evident…) of culverts with a related ‘field datasheet’ info. Click a point to see details.

Locations that have spatial data but no corredsponding data sheets.

Color coded by ‘Priority’

Generating SUMMARIES for field and desktop assessments.

This section will walk through the process of merging the various bits and bobs from the Tidal Culvert Workbook assessments (spreadsheets), the GIS-derived location data (shapefile above), desktop assessments (data housed in the workbooks) and partner priorities (our priority marsh complexes as well) into a list/table of culvert assessment needs to help steer the field and desktop needs. This list can be merged or compared (or replaced eventually?…) with the ‘MasterCrossingSpreadsheetChecklistEdit.xls’ found here:../../Box Sync/Tidal Assessments.
The resulting table will be updated regulalry and stored here: ../../Box Sync/Tidal Assessments